﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Configuration;
using System.Text;

namespace Team4
{
    // Data Access Layer - all code to access the SQL database goes in here.
    //                   - this code is accessed ONLY by BLL.class
    public class DAL
    {
        // Rename or make changes to this class as needed
        public class CalendarEvent_Data
        {
            public SqlInt32 Id { get; set; }
            public SqlString Title { get; set; }
            public SqlBoolean AllDay { get; set; }
            public SqlDateTime Start { get; set; }
            public SqlDateTime End { get; set; }
            public SqlString Url { get; set; }
            public SqlString ClassName { get; set; }
            public SqlBoolean Editable { get; set; }
            public SqlInt32 RoomCode { get; set; }
            public SqlString UserName { get; set; }

            public static CalendarEvent_Data[] GetEventsForRoomInRange(SqlDateTime startDateRange, SqlDateTime endDateRange, SqlInt32 roomCode)
            {
                return ReadDbForDateRangeAndResource(startDateRange, endDateRange, roomCode).ToArray();
            }

            #region Create, Read, Update, Delete Stored Procedure Calls go here.
            public static CalendarEvent_Data Create(CalendarEvent_Data ced)
            {
                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("INSERT INTO [CalendarEvent] ([Title], [AllDay], [Start], [End], [Url], [ClassName], [Editable], [RoomCode], [UserName]) ");
                sql.Append("VALUES (@Title, @AllDay, @Start, @End, @Url, @ClassName, @Editable, @RoomCode, @UserName) SET @Id = SCOPE_IDENTITY()");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                try
                {
                    cmd.Parameters.AddWithValue("@Title", ced.Title);
                    cmd.Parameters.AddWithValue("@AllDay", ced.AllDay);
                    cmd.Parameters.AddWithValue("@Start", ced.Start);
                    cmd.Parameters.AddWithValue("@End", ced.End);
                    cmd.Parameters.AddWithValue("@Url", ced.Url);
                    cmd.Parameters.AddWithValue("@ClassName", ced.ClassName);
                    cmd.Parameters.AddWithValue("@Editable", ced.Editable);
                    cmd.Parameters.AddWithValue("@RoomCode", ced.RoomCode);
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.User.Identity.Name);
                    var returnedIdParameter = new SqlParameter("@Id", SqlDbType.Int) { Direction = ParameterDirection.Output };
                    cmd.Parameters.Add(returnedIdParameter);

                    //////////////////////////////////////////////////////////////////
                    // Execute Query
                    //////////////////////////////////////////////////////////////////

                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    ced.Id = Convert.ToInt32(returnedIdParameter.Value);
                }
                catch (Exception ex)
                {
                    ced.Id = -1;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return ced;
            }

            public static CalendarEvent_Data Update(CalendarEvent_Data ced)
            {
                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("UPDATE [CalendarEvent] SET [Title] = @Title, [AllDay] = @AllDay, [Start] = @Start, [End] = @End,");
                sql.Append(" [Url] = @Url, [ClassName] = @ClassName, [Editable] = @Editable, [RoomCode] = @RoomCode, [UserName] = @UserName ");
                sql.Append("WHERE [Id] = @Id");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                try
                {
                    cmd.Parameters.AddWithValue("@Id", ced.Id);
                    cmd.Parameters.AddWithValue("@Title", ced.Title);
                    cmd.Parameters.AddWithValue("@AllDay", ced.AllDay);
                    cmd.Parameters.AddWithValue("@Start", ced.Start);
                    cmd.Parameters.AddWithValue("@End", ced.End);
                    cmd.Parameters.AddWithValue("@Url", ced.Url);
                    cmd.Parameters.AddWithValue("@ClassName", ced.ClassName);
                    cmd.Parameters.AddWithValue("@Editable", ced.Editable);
                    cmd.Parameters.AddWithValue("@RoomCode", ced.RoomCode);
                    cmd.Parameters.AddWithValue("@UserName", HttpContext.Current.User.Identity.Name);

                    //////////////////////////////////////////////////////////////////
                    // Execute Query
                    //////////////////////////////////////////////////////////////////

                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ced.Id = -1;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return ced;
            }

            public static CalendarEvent_Data Delete(CalendarEvent_Data ced)
            {
                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("DELETE FROM [CalendarEvent] WHERE [Id] = @Id");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                try
                {
                    cmd.Parameters.AddWithValue("@Id", ced.Id);

                    //////////////////////////////////////////////////////////////////
                    // Execute Query
                    //////////////////////////////////////////////////////////////////

                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    ced.Id = -1;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return ced;
            }

            // This can be replaced with a stored procedure, just here for reference or use.
            private static List<CalendarEvent_Data> ReadDbForDateRangeAndResource(SqlDateTime start, SqlDateTime end, SqlInt32 resource)
            {
                List<CalendarEvent_Data> result = new List<CalendarEvent_Data>();
                var grtDate = new SqlDateTime((DateTime.Today.Year - 2), DateTime.Today.Month, 1);

                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT [Id], [Title], [AllDay], [Start], [End], [Url], [ClassName], [Editable], [RoomCode], [UserName] ");
                sql.Append(" FROM [CalendarEvent] ");
                sql.Append(" WHERE ((( [Start] <= @end AND [End] >= @start ) ");
                sql.Append(" AND ");
                sql.Append(" [RoomCode] = @resource ))"); // Resource is a match (room)

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                SqlDataReader rdr;

                //////////////////////////////////////////////////////////////////
                // Execute Query
                //////////////////////////////////////////////////////////////////
                try
                {
                    // Define passed parameters with values
                    cmd.Parameters.AddWithValue("@start", start);
                    cmd.Parameters.AddWithValue("@end", end);
                    cmd.Parameters.AddWithValue("@resource", resource);

                    cmd.Connection.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        // Add calendar event retrieved to list here from rdr...
                        result.Add(GetEventFromReader(rdr));
                    }
                }
                catch
                {
                    result = null;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return result;
            }
            #endregion

            private static CalendarEvent_Data GetEventFromReader(SqlDataReader rdr)
            {
                CalendarEvent_Data newEvent = new CalendarEvent_Data();
                newEvent.Id = (rdr.GetInt32(rdr.GetOrdinal("Id"))); // Id
                newEvent.Title = (rdr.GetSqlString(rdr.GetOrdinal("Title"))); // Title
                newEvent.AllDay = rdr.IsDBNull(rdr.GetOrdinal("AllDay")) ? SqlBoolean.Null : (rdr.GetBoolean(rdr.GetOrdinal("AllDay")));
                newEvent.Start = rdr.IsDBNull(rdr.GetOrdinal("Start")) ? SqlDateTime.Null : (rdr.GetDateTime(rdr.GetOrdinal("Start")));
                newEvent.End = rdr.IsDBNull(rdr.GetOrdinal("End")) ? SqlDateTime.Null : (rdr.GetDateTime(rdr.GetOrdinal("End")));
                newEvent.Url = rdr.IsDBNull(rdr.GetOrdinal("Url")) ? SqlString.Null : (rdr.GetSqlString(rdr.GetOrdinal("Url")));
                newEvent.ClassName = rdr.IsDBNull(rdr.GetOrdinal("ClassName")) ? SqlString.Null : (rdr.GetSqlString(rdr.GetOrdinal("ClassName")));
                newEvent.Editable = rdr.IsDBNull(rdr.GetOrdinal("Editable")) ? SqlBoolean.Null : (rdr.GetBoolean(rdr.GetOrdinal("Editable")));
                newEvent.RoomCode = rdr.IsDBNull(rdr.GetOrdinal("RoomCode")) ? SqlInt32.Null : (rdr.GetInt32(rdr.GetOrdinal("RoomCode")));
                newEvent.UserName = rdr.IsDBNull(rdr.GetOrdinal("UserName")) ? SqlString.Null : (rdr.GetSqlString(rdr.GetOrdinal("UserName")));

                return newEvent;
            }
        }

        // Rename or make changes to this class as needed
        public class Room_Data
        {
            public SqlInt32 Id { get; set; }
            public SqlString Name { get; set; }
            public SqlString Number { get; set; }

            #region Create, Read, Update, Delete Stored Procedure Calls go here.
            // This will access the database tables via stored procedure calls

            public static Room_Data Create(Room_Data rd)
            {
                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("INSERT INTO [Room] ([Name], [Number]) ");
                sql.Append("VALUES (@Name, @Number) SET @Id = SCOPE_IDENTITY()");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                try
                {
                    cmd.Parameters.AddWithValue("@Name", rd.Name);
                    cmd.Parameters.AddWithValue("@Number", rd.Number);
                    var returnedIdParameter = new SqlParameter("@Id", SqlDbType.Int)
                                                  {Direction = ParameterDirection.Output};
                    cmd.Parameters.Add(returnedIdParameter);

                    //////////////////////////////////////////////////////////////////
                    // Execute Query
                    //////////////////////////////////////////////////////////////////

                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                    rd.Id = Convert.ToInt32(returnedIdParameter.Value);
                }
                catch (Exception ex)
                {
                    rd.Id = -1;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return rd;
            }

            public static Room_Data Read(Int32 id)
            {
                Room_Data result = new Room_Data();

                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT [Id], [Name], [Number] ");
                sql.Append(" FROM [Room] WHERE [Id] = @Id ");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                cmd.Parameters.AddWithValue("@Id", id);

                //////////////////////////////////////////////////////////////////
                // Execute Query
                //////////////////////////////////////////////////////////////////
                try
                {
                    cmd.Connection.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    result = GetRoomFromReader(rdr);
                }
                catch
                {
                    result = null;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return result;
            }

            public static Room_Data Update(Room_Data rd)
            {
                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("UPDATE [Room] SET [Name] = @Name, [Number] = @Number ");
                sql.Append("WHERE [Id] = @Id");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                try
                {
                    cmd.Parameters.AddWithValue("@Id", rd.Id);
                    cmd.Parameters.AddWithValue("@Name", rd.Name);
                    cmd.Parameters.AddWithValue("@Number", rd.Number);

                    //////////////////////////////////////////////////////////////////
                    // Execute Query
                    //////////////////////////////////////////////////////////////////

                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    rd.Id = -1;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return rd;
            }

            public static Room_Data Delete(Room_Data rd)
            {
                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("DELETE FROM [Room] WHERE [Id] = @Id");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn =
                        new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                try
                {
                    cmd.Parameters.AddWithValue("@Id", rd.Id);

                    //////////////////////////////////////////////////////////////////
                    // Execute Query
                    //////////////////////////////////////////////////////////////////

                    cmd.Connection.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    rd.Id = -1;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return rd;
            }

            #endregion

            // This can be replaced with a stored procedure, just here for reference or use.
            public static List<BLL.Room> GetListofRooms()
            {
                List<BLL.Room> rooms = new List<BLL.Room>();

                foreach (Room_Data rmd in GetRooms())
                {
                    rooms.Add(new BLL.Room() { Id = rmd.Id.Value, Name = rmd.Name.Value, Number = rmd.Number.Value });
                }

                return rooms;
            }
            private static List<Room_Data> GetRooms()
            {
                List<Room_Data> result = new List<Room_Data>();

                //////////////////////////////////////////////////////////////////
                // Create Query String
                //////////////////////////////////////////////////////////////////
                StringBuilder sql = new StringBuilder();
                sql.Append("SELECT [Id], [Name], [Number] ");
                sql.Append(" FROM [Room] ");

                //////////////////////////////////////////////////////////////////
                // Instantiate SQL Objects
                //////////////////////////////////////////////////////////////////
                var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
                var cmd = new SqlCommand(sql.ToString(), conn);
                SqlDataReader rdr;

                //////////////////////////////////////////////////////////////////
                // Execute Query
                //////////////////////////////////////////////////////////////////
                try
                {
                    cmd.Connection.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        // Add calendar event retrieved to list here from rdr...
                        result.Add(GetRoomFromReader(rdr));
                    }
                }
                catch
                {
                    result = null;
                }
                finally
                {
                    cmd.Connection.Close();
                }

                return result;
            }

            private static Room_Data GetRoomFromReader(SqlDataReader rdr)
            {
                Room_Data newRoom = new Room_Data();
                newRoom.Id = (rdr.GetInt32(rdr.GetOrdinal("Id")));
                newRoom.Name = (rdr.GetSqlString(rdr.GetOrdinal("Name")));
                newRoom.Number = (rdr.GetSqlString(rdr.GetOrdinal("Number")));

                return newRoom;
            }
        }
    }
}